Hive元数据库查询SQL
列出hive元数据库中所有表所拥有的分区个数
use metastore;select d.NAME, a.TBL_NAME, b.count, c.LOCATION from TBLS a join (select TBL_ID,count(*) as count from PARTITIONS group by TBL_ID ) b join SDS c join DBS d where a.TBL_ID=b.TBL_ID and a.SD_ID=c.SD_ID and a.DB_ID=d.DB_ID order by b.count;
列出hive元数据库中单个database中所有表所拥有的分区个数
use metastore;select d.NAME, a.TBL_NAME, b.count, c.LOCATION from TBLS a join (select TBL_ID,count(*) as count from PARTITIONS group by TBL_ID ) b join SDS c join DBS d on a.TBL_ID=b.TBL_ID and a.SD_ID=c.SD_ID and a.DB_ID=d.DB_ID and d.NAME='dly' order by b.count;
列出hive元数据库中,所有Database中所有外部表所对应的HDFS路径
use metastore;select d.NAME,a.TBL_NAME,a.TBL_TYPE,c.LOCATION from TBLS a join SDS c join DBS d where a.SD_ID=c.SD_ID and a.DB_ID=d.DB_ID and a.TBL_TYPE='EXTERNAL_TABLE' order by d.NAME;